use [family-annal]
GO
-- Drop trigger in table tb_fa_identity_passport
IF OBJECT_ID ('tb_fa_identity_passport.trigger_fa_identity_passport_insert_update','TR') IS NOT NULL
   DROP TRIGGER tb_fa_identity_passport.trigger_fa_identity_passport_insert_update;
GO
-- Drop table and all constraint if exists
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tb_fa_identity_passport]') AND name = N'pk_tb_fa_identity_passport')
BEGIN
ALTER TABLE [dbo].[tb_fa_identity_passport] DROP CONSTRAINT [pk_tb_fa_identity_passport]
END
GO
USE [family-annal]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_fa_identity_passport]') AND type in (N'U'))
DROP TABLE [dbo].[tb_fa_identity_passport]
GO
use [family-annal]
GO
-- create table identity passport
create table tb_fa_identity_passport
(
	identity_passport_id int not null,
	person_id int, -- references to tb_fa_person.person_id
	identity_passport_info nvarchar(max), -- use defined string json on c# source code
	create_date datetime,
	create_uid int,
	update_date datetime,
	update_uid int,
	identity_passport_status varchar(1) default 'Y'
);
GO
-- create constraint pk_tb_fa_identity_passport primary key
--alter table tb_fa_identity_passport
--		add constraint pk_tb_fa_identity_passport primary key (identity_passport_id);
--GO
-- create constraint fk_tb_fa_identity_passport foreign key
--alter table tb_fa_identity_passport
--		add constraint fk_tb_fa_identity_passport foreign key (person_id)
--		references tb_fa_person(person_id);
--GO
use [family-annal];
GO
-- create trigger trigger_fa_identity_passport_insert_update
CREATE TRIGGER trigger_fa_identity_passport_insert_update
   ON  tb_fa_identity_passport 
   AFTER INSERT, UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
    IF EXISTS(SELECT * FROM INSERTED)
    BEGIN
		update tb_fa_identity_passport set create_date = GETDATE()
		from tb_fa_identity_passport t join inserted i
		on t.identity_passport_id = i.identity_passport_id
    END
    IF NOT EXISTS(SELECT * FROM UPDATED)
    BEGIN
		update tb_fa_identity_passport set update_date = GETDATE()
		from tb_fa_identity_passport t join updated u
		on t.identity_passport = u.identity_passport;
    END
END
GO